from settings import WcSettings
from xlrd import open_workbook

import re
import datetime
import xlsxwriter
import os

etf_list = [str(x) for x in list(set(WcSettings.ETF_LIST))]
file_name = WcSettings.FILE_NAME
add_file_url = WcSettings.ADD_FILE
log_url = WcSettings.ADD_FILE

sheet_data = {}
datas = []


def log_file(values, url=None):
    with open(os.path.join(log_url, 'log.txt'), 'a+', encoding='utf') as f:
        if not url:
            f.write('\n' + 'v' * 20 + '\n')
            for line in values:
                msg = f'未找到：{line}\n'
                f.write(msg)
                print(end=msg)
        else:
            msg = f'在`{url}`找到了 {values} 已拆分\n'
            f.write(msg)
            print(end=msg)


# 判断时间
def validate(date_list):
    for date_text in date_list:
        try:
            d_time = datetime.datetime.strptime(date_text, '%Y%m%d')
            d_time1 = datetime.datetime.strptime('20200101', '%Y%m%d')
            if d_time1 < d_time and len(date_text) == 8:
                return date_text
        except:
            pass


# 格式化数据
listNames = ['证券代码', '证券名称', '实际数量', '清单股数', '替代标志']


def formatting_dict(tb):
    dict = []
    for i in range(0 if '代码' not in tb.cell_value(0, 0) else 1, tb.nrows):
        data = {}
        for j in range(tb.ncols):
            if '代码' not in tb.cell_value(0, 0) and len(listNames) > j:
                title = listNames[j]
            elif '代码' in tb.cell_value(0, 0):
                title = tb.cell_value(0, j)
            else:
                title = ''
            value = tb.cell_value(i, j)
            data[title] = value
        dict.append(data)
    return dict


def formatting_list(tb):
    data = []
    # 循环行
    for r in range(0, tb.nrows):
        val = []
        # 循环列数
        for c in range(0, tb.ncols):
            val.append(tb.cell_value(r, c))
        data.append(val)

    return data


# 保存数据
def add_file(filename, file_list, url, code):
    workbook = xlsxwriter.Workbook(filename)
    worksheet = workbook.add_worksheet()  # 创建工作表
    for row in range(len(file_list)):
        for train in range(len(file_list[row])):
            # 证券代码、 清单股数、实际数量
            worksheet.write(row, train, file_list[row][train])  # 写入数据
    workbook.close()  # 关闭xlsx文件
    log_file(code, url)


# 获取数据库信息
def get_workbook(get_url):
    # 获取所有数据
    if '~$' not in get_url:
        wb = open_workbook(get_url)
        for sheet in wb.sheet_names():
            name = get_url.split('\\')[-1]
            date_list = re.findall(r'\d*\d', name)
            tb = wb.sheets()[wb.sheet_names().index(sheet)]
            if sheet in etf_list:
                data = formatting_list(tb)
                add_file(os.path.join(add_file_url, file_name.format(_code=sheet, _date=validate(date_list))), data,
                         get_url, sheet)
                WcSettings.ETF_LIST.remove(int(sheet))
            else:
                for code in etf_list:
                    if code in date_list:
                        data = formatting_list(tb)
                        add_file(os.path.join(add_file_url, file_name.format(_code=code, _date=validate(date_list))),
                                 data, get_url, code)
                        WcSettings.ETF_LIST.remove(int(code))


if __name__ == '__main__':
    get_workbook(r"E:\桌面\wc_data\转发：【hx】清单\ETF588000_20230411.xls")
